﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for DataService
/// </summary>
public class DataService
{
	string connectString=ConfigurationManager.ConnectionStrings["QLBV"].ConnectionString;
    SqlConnection con;

    public bool OpenConnect()
    {
        try
        {
            con = new SqlConnection(connectString);
            con.Open();
            return true;
        }
        catch
        {
            return false;
        }
    }

    public void CloseConnect()
    {
        con.Close();
    }

    public void ExcuteNoneQueryWithSP(string nameSP, params SqlParameter[] lst)
    {
        OpenConnect();
        SqlCommand cmd = new SqlCommand(nameSP, con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(lst);
        cmd.ExecuteNonQuery();
        CloseConnect();
    }

    // Lấy dữ liệu bằng Store Procedure
    public DataTable GetDataBySP(string nameSP, params SqlParameter[] lst)
    {
        OpenConnect();
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand(nameSP, con);
        cmd.Parameters.AddRange(lst);
        cmd.CommandType = CommandType.StoredProcedure;
        dt.Load(cmd.ExecuteReader());
        CloseConnect();
        return dt;
    }

    public DataTable GetData(string sql, params SqlParameter[] lst)
    {
        OpenConnect();
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddRange(lst);
        dt.Load(cmd.ExecuteReader());
        CloseConnect();
        return dt;
    }
}